
----百世出入库统计汇总报表  省份监控汇总
         insert overwrite table jms_dm.dm_service_site_out_agg_dt partition(dt)
         select
             dt as site_in_date,
             min(tmp.province) as province,
             min(tmp.city) as city,
             sum(service_site_cnt) as service_site_cnt,
             sum(site_in_cnt) as site_in_cnt,
             sum(site_out_cnt) as site_out_cnt,
             sum(site_abnormal_out_cnt) as site_abnormal_out_cnt,
             sum(site_current_out_cnt) as site_current_out_cnt,
             sum(site_current_end_cnt) as site_current_end_cnt,
             sum(site_t1_out_cnt) as site_t1_out_cnt,
             sum(site_t1_end_cnt) as site_t1_end_cnt,
             sum(site_t3_out_cnt) as site_t3_out_cnt,
             sum(site_t3_end_cnt) as site_t3_end_cnt,
             sum(site_t6_out_cnt) as site_t6_out_cnt,
             sum(site_t6_end_cnt) as site_t6_end_cnt,
             agency_code,       --代理区code
             max(agency_name) as agency_name,       --代理区名字
             province_code,     --省code
             city_code,         --城市code
             area_code,  --区县code
             max(area) as area,       --区县名字
             dt
         from
         (
             select
                 agency_code,
                 province_code,
                 city_code,
                 max(agency_name) as agency_name,
                 min(province) as province,
                 min(city) as city,
                 area_code, --区县code
                 max(area) as area,  --区县名字
                 servicesitecode,
                 count(distinct servicesitecode) as service_site_cnt, --服务点数
                 sum(if(isInSiteFlag==1, 1, 0)) as site_in_cnt, --入库量
                 sum(if(isInSiteFlag==0 and statuscode==40, 1, 0)) as site_out_cnt, --出库量
                 sum(if(isInSiteFlag==0 and statuscode==70, 1, 0)) as site_abnormal_out_cnt, --异常出库量
                 sum(if(self_pickup_current_billcode is not null and self_pickup_current_statuscode==40, 1, 0)) as site_current_out_cnt, --当日出库量
                 sum(if(self_pickup_current_billcode is not null and self_pickup_current_statuscode in (40,70), 1, 0)) as site_current_end_cnt, --当日完结量
                 sum(if(self_pickup_1_billcode is not null and self_pickup_1_statuscode==40, 1, 0)) as site_t1_out_cnt, --T+1日出库量
                 sum(if(self_pickup_1_billcode is not null and self_pickup_1_statuscode in (40,70), 1, 0)) as site_t1_end_cnt, --T+1日完结量
                 sum(if(self_pickup_3_billcode is not null and self_pickup_3_statuscode==40, 1, 0)) as site_t3_out_cnt, --T+3日出库量
                 sum(if(self_pickup_3_billcode is not null and self_pickup_3_statuscode in (40,70), 1, 0)) as site_t3_end_cnt, --T+3日完结量
                 sum(if(self_pickup_6_billcode is not null and self_pickup_6_statuscode==40, 1, 0)) as site_t6_out_cnt, --T+6日出库量
                 sum(if(self_pickup_6_billcode is not null and self_pickup_6_statuscode in (40,70), 1, 0)) as site_t6_end_cnt, --T+6日完结量
                 dt
             from jms_dm.dm_service_site_out_statistics_dt
             where dt between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and '{{ execution_date | cst_ds }}'
             group by agency_code,province_code,city_code,servicesitecode,dt,area_code
         ) tmp where tmp.site_in_cnt > 0
               group  by tmp.agency_code,tmp.province_code,tmp.city_code,tmp.area_code,dt
        distribute by dt
         ;

----百世出入库统计品牌汇总报表  按品牌汇总
         insert overwrite table jms_dm.dm_service_site_out_brand_agg_dt partition(dt)
         select
             service_site.dt as site_in_date,
             max(province),
             max(city),
             expresscompanycode,
             sum(if(detail.isInSiteFlag==1, 1, 0)) as site_in_cnt, --入库量
             sum(if(detail.isInSiteFlag==0 and detail.statuscode==40, 1, 0)) as site_out_cnt, --出库量
             sum(if(detail.isInSiteFlag==0 and detail.statuscode==70, 1, 0)) as site_abnormal_out_cnt, --异常出库量
             sum(if(detail.self_pickup_current_billcode is not null and detail.self_pickup_current_statuscode==40, 1, 0)) as site_current_out_cnt, --当日出库量
             sum(if(detail.self_pickup_current_billcode is not null and detail.self_pickup_current_statuscode in (40,70), 1, 0)) as site_current_end_cnt, --当日完结量
             sum(if(detail.self_pickup_1_billcode is not null and detail.self_pickup_1_statuscode==40, 1, 0)) as site_t1_out_cnt, --T+1日出库量
             sum(if(detail.self_pickup_1_billcode is not null and detail.self_pickup_1_statuscode in (40,70), 1, 0)) as site_t1_end_cnt, --T+1日完结量
             sum(if(detail.self_pickup_3_billcode is not null and detail.self_pickup_3_statuscode==40, 1, 0)) as site_t3_out_cnt, --T+3日出库量
             sum(if(detail.self_pickup_3_billcode is not null and detail.self_pickup_3_statuscode in (40,70), 1, 0)) as site_t3_end_cnt, --T+3日完结量
             sum(if(detail.self_pickup_6_billcode is not null and detail.self_pickup_6_statuscode==40, 1, 0)) as site_t6_out_cnt, --T+6日出库量
             sum(if(detail.self_pickup_6_billcode is not null and detail.self_pickup_6_statuscode in (40,70), 1, 0)) as site_t6_end_cnt, --T+6日完结量
             agency_code,
             max(agency_name),
             province_code,
             city_code,
             service_site.dt
         from
         (
             select servicesitecode,dt from
             (
             select servicesitecode, count(1) as cnt, dt from jms_dm.dm_service_site_out_statistics_dt
              where dt between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and '{{ execution_date | cst_ds }}'
                and isInSiteFlag=1
              group by servicesitecode,dt
             ) tmp where cnt>0
         ) service_site
         left join
         (
         select   billcode,
             case when expresscompanycode in('JTKD','ZTO','YUNDA','YTO','STO','ZGYZ','BESTEXP','EMS','FWEXP','SFEXP','JD','ZMKM') then expresscompanycode else 'OTHERS' end as expresscompanycode,
             province,city,servicesitecode,servicesitename,statuscode,
             self_pickup_current_billcode,
             self_pickup_current_statuscode,
             self_pickup_1_billcode,
             self_pickup_1_statuscode,
             self_pickup_3_billcode,
             self_pickup_3_statuscode,
             self_pickup_6_billcode,
             self_pickup_6_statuscode,
             isInSiteFlag,
             agency_code ,
             agency_name ,
             province_code ,
             city_code ,
             dt
         from jms_dm.dm_service_site_out_statistics_dt
        where dt between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and '{{ execution_date | cst_ds }}'
         ) detail
           on detail.servicesitecode=service_site.servicesitecode and detail.dt=service_site.dt
         where detail.servicesitecode is not null
         group by agency_code,province_code,city_code,service_site.dt,expresscompanycode
         distribute by dt
         ;

----百世出入库统计服务点汇总报表  按服务点汇总

        insert overwrite table jms_dm.dm_service_site_out_station_agg_dt partition(dt)
        select
        t2.dt as site_in_date
        ,t2.province
        ,t2.servicesitecode
        ,t2.servicesitename
        ,t2.city
        ,t2.area as county
        ,t1.address
        ,t1.phone as tel
        ,t2.site_in_cnt
        ,t2.site_out_cnt
        ,t2.site_abnormal_out_cnt
        ,t2.site_current_out_cnt
        ,t2.site_current_end_cnt
        ,t2.site_t1_out_cnt
        ,t2.site_t1_end_cnt
        ,t2.site_t3_out_cnt
        ,t2.site_t3_end_cnt
        ,t2.site_t6_out_cnt
        ,t2.site_t6_end_cnt
        ,if(nvl(site_in_cnt,0) = 0,0,1) as site_in_flag
        ,t2.agency_code --           string comment '代理区code'
        ,t2.agency_name --           string comment '代理区名字'
        ,t2.province_code --         string comment '省code'
        ,t2.city_code --             string comment '城市code'
        ,t2.area_code --             string comment '区code'
        ,t2.area --                  string comment '区'
        ,t2.cooperate_company as expresscompanycode
        ,t1.channel_code           --渠道
        ,t1.cooperate_network      --合作网点code
        ,t2.delivery_site_in_cnt   --送货上门入库量
        ,t2.delivery_site_out_cnt  --送货上门出库量
        ,t2.dt
        from
            (select * from (
              select agency_code,province_code,city_code,area_code,cooperate_company,servicesitecode,
                              max(agency_name) as agency_name,
                              max(province)                                             as province,
                              max(city)                                                 as city,
                              max(area) as area,
                              max(servicesitename) as servicesitename,
                              count(distinct (servicesitecode))                         as service_site_cnt,      --服务点数
                              sum(if(isInSiteFlag == 1, 1, 0))                          as site_in_cnt,           --入库量
                              sum(if(isInSiteFlag == 0 and statuscode == 40, 1, 0))     as site_out_cnt,          --出库量
                              sum(if(isInSiteFlag == 0 and statuscode == 70, 1, 0))     as site_abnormal_out_cnt, --异常出库量
                              sum(if(self_pickup_current_billcode is not null and
                                     self_pickup_current_statuscode == 40, 1, 0))                                                 as site_current_out_cnt,  --当日出库量
                              sum(if(self_pickup_current_billcode is not null and
                                     self_pickup_current_statuscode in (40, 70), 1, 0))                                           as site_current_end_cnt,  --当日完结量
                              sum(if(self_pickup_1_billcode is not null and self_pickup_1_statuscode == 40, 1, 0))                as site_t1_out_cnt,       --T+1日出库量
                              sum(if(self_pickup_1_billcode is not null and self_pickup_1_statuscode in (40, 70), 1,  0))         as site_t1_end_cnt,       --T+1日完结量
                              sum(if(self_pickup_3_billcode is not null and self_pickup_3_statuscode == 40, 1, 0))                as site_t3_out_cnt,       --T+3日出库量
                              sum(if(self_pickup_3_billcode is not null and self_pickup_3_statuscode in (40, 70), 1, 0))          as site_t3_end_cnt,       --T+3日完结量
                              sum(if(self_pickup_6_billcode is not null and self_pickup_6_statuscode == 40, 1, 0))                as site_t6_out_cnt,       --T+6日出库量
                              sum(if(self_pickup_6_billcode is not null and self_pickup_6_statuscode in (40, 70), 1, 0))          as site_t6_end_cnt,       --T+6日完结量
                              sum(if(isInSiteFlag == 1 and deliveryflag = 1, 1, 0))                          as delivery_site_in_cnt,           --送货上门入库量
                              sum(if(isInSiteFlag == 0 and statuscode == 40 and deliveryflag = 1, 1, 0))     as delivery_site_out_cnt,          --送货上门出库量
                              dt
                       from jms_dm.dm_service_site_out_statistics_dt
                       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and '{{ execution_date | cst_ds }}'
                       group by agency_code,province_code,city_code,area_code,cooperate_company,servicesitecode,dt
                   ) tmp where tmp.site_in_cnt > 0 or tmp.site_out_cnt > 0
          ) t2
          left join
          (
            select
                   service_site_code as servicesitecode,
                   max(address) as address,
                   max(phone) as phone,
                   max(channel_code) as channel_code,
                   max(cooperate_network) as cooperate_network
            from jms_dim.dim_lq_service_site_new_base
              group by service_site_code
            ) t1
        on t2.servicesitecode = t1.servicesitecode
        distribute by dt
        ;